Member Filter Builder

The Member Filter Builder dialog box makes it simple to build complex member filters without having to remember or look up the proper syntax.

On the Cube Views page, select a cube view. Select Rows and Columns and then select a row or column header. Click the Member Filters tab and then Member Filter Builder to launch the dialog box.

Navigation

Hover over the items in the Member Filter Builder dialog box for additional information.

1. Member Filter field is a white text box. 2. Dimension Tokens are buttons with a white background and black text. 3. Member Expansion Functions are a list with white background that are shaded when an item is selected. 4. Member Expansion Where Clause is a list with a white background that is shaded when an item is selected. 5. Time Functions is a blue tab. 6. Variables is a blue tab. 7. Samples is a blue tab. 8. Expansion is a list of buttons with a white background and dark text. 9. Workflow is a list of buttons with a white background and dark text. 10. Other is a list of buttons with a white background and dark text.

  1. Member Filter: Location where the member filter is built. Type in this section or use the other items in the dialog box to enter information into the field.

  2. Dimension Tokens: There is a button for each dimension that launches the appropriate selection dialog box.

  3. Member Expansion Functions: Double-click a member expansion to add it to the member filter.

  4. Member Expansion Where Clause: If you select Member Expansion Where, use the where clause properties to complete the expression.
    Example: UD2#AllProducts.Children.Where(Name Contains Clubs)

  5. Time Functions: Only applies to the Time dimension, such as T#POVPrior1. Double-click a time function to add it.

  6. Substitution Variables: Double-click a system-wide substitution variable to add it.

  7. Samples: Refer to this tab for example syntax for complex queries and calculations, including how to build member expansions, where clause expressions, time expressions, calculations (GetDataCell and column/row expressions), custom member list expressions, and XFBR, XFCell, and XFGetMemberProperty.

  8. Expansion: Commonly used member expansions added to the end of filters. Double-click the expansion to add it.

  9. Workflow: Commonly used workflow member expansions used in cube views that point to a report, form, or dashboard and are affiliated with a specific workflow profile.

  10. Other: Commonly used member filter functions that enable you to create calculated rows and columns or use a custom parameter to store member lists.

Member Expansion Tab

To reduce cube view maintenance, use simple member expansions and connect your cube views to the metadata when complex expansions are needed. Avoid referencing individual members.

  • Reverse order expansions: These selections include ChildrenInclusiveR, TreeDescendantsR, and TreeDescendantsInclusiveR.

  • Where clauses: Enable further qualification of the results and can pull members based on properties such as "name contains" or text properties.

  • Remove functions: Removes some of the members from the results.

  • Parents: Returns the direct parent of the member. There may be multiple parents if there are alternate hierarchies.

  • Ancestors: Returns all members up the chain from the original member.

  • Options: Advanced option if you are a report builder familiar with the extensibility design in your application. This expansion is commonly used to focus on one portion of your extensible dimensionality.

This is not an exhaustive list of all the member expansions. Each expansion can also be found in the Samples tab. Hover over each to see the purpose and a sample of the expected syntax. Double-click the member expansion to place the sample syntax in the Member Filter field.

Use the member filter to filter data by creating a list of restricted members. Member filters can contain multiple member scripts. A member script queries a defined set of dimensional members. Members can be specified for any or all dimensions and the primary dimension can also specify a member expansion formula (for example, Descendants).

For example, a simple member script that returns the year 2022: T#2022.

For example, a member script with a member expansion that returns all the income statement accounts: A#[Income Statement].Descendants.

If one or a few dimensions in the member script have a dimension token, the remaining dimensions are pulled from the Cube View POV, the Global POV, or Workflow, Time, and Scenario. Separate each dimension that has a token in the member script with a colon as in the following example:

Cb#GolfStream:E#Houston:P#Texas:C#Local:S#Budget:T#2022M3:V#YTD:A#60000:F#None:
O#Forms:IC#None: U1#Sales: U2#HybridXL: U3#Northeast: U4#SportsCo: U5#None: U6#None: U7#None: U8#None

For example, a member filter that contains three different member scripts that returns the Actual, Budget, and Forecast scenarios: S#Actual, S#Budget, S#Forecast.

Time Functions Tab

Time is a fixed dimension and is based on the time dimension type associated with the application. OneStream software has custom time dimensions (for example, weekly and monthly), which affect time functions and expansions. Time functions pivot the member, and time expansions extend the member.

  • Time function example to display the prior period of the time member in a Cube POV: T#POVPrior1

  • Time expansion example to display all months in the year 2022: T#2022.Months

Add a time function from the Time Functions tab:

  1. In the Member Filter Builder dialog box, click the Time Functions tab.

  2. Select the type of time function to display the examples available: POV, WF, Global, or General. You can also select All.

  3. Double-click a function to populate it in the Member Filter field.

    NOTE: Click the Samples tab to view more time functions.

The following time function example demonstrates a style of syntax that provides flexibility. You can choose from a variety of time functions, but this one enables you to pivot easily on the year and period separately:

T#YearPrior1(|PovTime|)PeriodNext1 (|PovTime|)

  • Prior year from the POV time: T#YearPrior1(|PovTime|)

  • Next period from the POV time: PeriodNext1 (|PovTime|)

Variables Tab

Substitution variables are short scripts that use pipe characters to include a predefined substitution variable. For example, |WFProfile| would refresh to display the current workflow profile name. They come with every installation of OneStream and cannot be edited, so you do not need to create or maintain them.

Substitution variables can be used throughout the application. For cube views, you can use them for:

  • Headers and footers

  • Rows and columns

  • Cube view page captions

Substitution variables are always referenced with pipes (for example, |POVTime|). The Member Filter Builder dialog box includes substitution variables in the following categories:

  • POV (Cube POV)

  • WF (Workflow POV)

  • Global (Global POV)

  • CV (Cube View POV)

  • MF (Member Filter)

  • General (items not related to a POV)

When using a substitution variable to return a member name, the prefix indicates where the value of the variable is pulled from. Choose if it should refresh based on the Cube View POV (CV), Workflow POV (WF), Cube POV (POV), Member Filter (MF), or Global POV (Global).

Choose if you want to display the member description (usually in headers and footers) by adding Desc as the suffix. Time has an added short description option that is set through the time profiles. For example, |WFTimeDesc| returns the description Feb 2022, and |WFTimeShortDesc| returns the short description Feb.

Common substitution variables:

  • Username that ran the report: |UserName|

  • Cube view name: |CVName|

  • Members text properties: |Text1|

  • Today's date: |DateDDMMYYYY|

On the Variables tab, you can double-click a substitution variable to add it to a member filter. You can also copy a substitution variable from the Object Lookup dialog box:

  1. On the Cube Views page, click Object Lookup to open the dialog box.

  2. Under Object Type, select Substitution Variables.

  3. Select a substitution variable from the list. Use the Filter field to find a specific option.

  4. Click Copy to Clipboard and then paste the substitution variable where needed.

Samples Tab

The Samples tab holds example syntax for more complex queries and calculations in the following categories:

  • Member Expansions

  • Where Clause Expressions

  • Time Expressions

  • Calculations (GetDataCell Expressions and Column/Row Expressions)

  • Custom Member List Expressions

  • XFBR, XFCell, and XFGetMemberProperty

Add an expression from the Samples tab:

  1. In the Member Filter Builder dialog box, click the Samples tab.

  2. Double-click a sample to populate it in the Member Filter field.

Where Clauses

Where clauses are commonly used in reporting to create a more flexible query. The where clause can pull members based on properties:

  • Text properties

  • Portions of the description or name (for example, starts with or contains)

  • Security

  • Account types (account dimension only)

  • Intercompany (entity and account dimension only)

  • Specific currency (entity dimension only)

  • In use property

  • Has children

    NOTE: Where(HasChildren = True) can only be used for the dimensions assigned to the cube on the default Scenario Type.

  • Has a member formula

NOTE: Click the Samples tab to view common where clauses.

Add a where clause from the Samples tab:

  1. In the Member Filter Builder dialog box, click the Samples tab.

  2. Expand the Where Clause Expressions list.

  3. Double-click a sample to populate it in the Member Filter field.